if exists (select 1 from sysobjects where name = 'get_personjprow' and type = 'P')
begin
   drop procedure get_personjprow
   print 'Procedure: get_personjprow deleted ...'
end
go
create procedure get_personjprow(
  @prsid      int = 1,
  @jahrid     int = 1
)
as
begin
  set nocount on

  select ps.AbteilungID, 
         a.Name, 
         ap.PlanID, 
         ap.Name,
         at.Name

    from PlanSchema ps
    left outer join ArbeitsPlan ap
      on ap.SchemaID = ps.SchemaID
    left outer join PlanungsJahr pj
      on pj.JahrID = ap.JahrID
    left outer join Abteilung a
      on a.AbteilungID = ps.AbteilungID
    left outer join ArbeitsTage at
      on at.ATID = ap.ATID
   where ps.PrsID = @prsid
     and ap.PlanID is not null
     and pj.JahrID = @jahrid
   order by ps.AbteilungID, ap.PlanID

end
go
print 'Procedure: get_personjprow done ...'
go

grant exec on get_personjprow to prsadmins with grant option
go
grant exec on get_personjprow to prsusers
go

